﻿using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }
    /// <summary>
    /// This method will be called from the client side using PageMethods
    /// and its primary responsibility is to fetch records from the database
    /// using that startRowIndex and MaximumRows allowed
    /// </summary>
    /// <param name="PageNumber">Page Number of the grid</param>
    /// <param name="MaximumRows">Page Size</param>
    /// <returns>Rows in the form of a list</returns>
    [System.Web.Services.WebMethod]
    public static ProductList GetProducts(int PageNumber,int MaximumRows)
    {
        //calculating the start row index
        int Start = PageNumber + 1;
        //calculating the end row index
        int End = PageNumber + MaximumRows;
        ProductList productList = new ProductList();
        //setting up sql connection to execute the query
        SqlConnection sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
        sqlConnection.Open();
        //setting up the command object with a parameterized query and sql connection
        using (SqlCommand command = new SqlCommand(GetQuery(), sqlConnection))
        {
            //Creating Sql Start Paramter 
            SqlParameter StartParameter = new SqlParameter();
            StartParameter.DbType = DbType.Int32;
            StartParameter.ParameterName = "startRowIndex";
            StartParameter.Value = Start;
            //Creating Sql End Parameter
            SqlParameter EndParameter = new SqlParameter();
            EndParameter.DbType = DbType.Int32;
            EndParameter.ParameterName = "endRowIndex";
            EndParameter.Value = End;
            //Adding Start Parameter to the command object
            command.Parameters.Add(StartParameter);
            //Adding End Parameter to the command object
            command.Parameters.Add(EndParameter);
            //Executing Reader
            using (IDataReader reader = command.ExecuteReader())
            {
                //Creating a list of objects from the fetched rows
                while (reader.Read())
                {
                    productList.lstProducts.Add(ConstructProduct(reader));
                }
            }
        }
        //closing the connection
        sqlConnection.Close();
        //Assigning total row count for the given query so that it can
        //be used by the grid to set up total number of pages
        productList.RowCount = GetRowCount();
        return productList;
    }
    /// <summary>
    /// Builds the Actual Parameterized Query which handles On-Demand Paging using
    /// ROW_NUMBER() feature given by SQL Server 2005
    /// </summary>
    /// <returns>Parameterized Query</returns>
    private static String GetQuery()
    {
        
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.Append(" SELECT * FROM ( ");
        stringBuilder.Append(" SELECT	ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS row_num,ProductID,[Name],ProductNumber,SafetyStockLevel,ReorderPoint ");
        stringBuilder.Append(" FROM	Product ");
        stringBuilder.Append(" ) AS TempTable WHERE row_num>=@startRowIndex AND row_num<=@endRowIndex ");
        return stringBuilder.ToString();
    }
    /// <summary>
    /// Builds the Query such that it return the count of the
    /// total number of account the query will select without
    /// paging
    /// </summary>
    /// <returns>Query to return total number of records</returns>
    private static String GetRowCountQuery()
    {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.Append(" SELECT COUNT(*) FROM ( ");
        stringBuilder.Append(" SELECT	ProductID,[Name],ProductNumber,SafetyStockLevel,ReorderPoint ");
        stringBuilder.Append(" FROM	Product ");
        stringBuilder.Append(" ) AS TempTable");
        return stringBuilder.ToString();
    }
    /// <summary>
    /// Executes the Actual Query without paging in order to
    /// fetch total number of records
    /// </summary>
    /// <returns>Total Number of records</returns>
    private static int GetRowCount()
    {
        int rowCount;
        SqlConnection sqlConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True");
        sqlConnection.Open();
        using (SqlCommand command = new SqlCommand(GetRowCountQuery(), sqlConnection))
        {
            rowCount = Convert.ToInt32(command.ExecuteScalar());
        }
        sqlConnection.Close();
        return rowCount;

    }
    /// <summary>
    /// Constructs Product Entity from the IDataReaer
    /// </summary>
    /// <param name="reader">Reader (Already Executed)</param>
    /// <returns>Product Entity</returns>
    private static Product ConstructProduct(IDataReader reader)
    {
        Product product = new Product();

        int i = reader.GetOrdinal("ProductID");
        if (!reader.IsDBNull(i))
            product.ProductID = reader.GetInt32(i);
        i = reader.GetOrdinal("Name");
        if (!reader.IsDBNull(i))
            product.Name = reader.GetString(i);
        i = reader.GetOrdinal("ProductNumber");
        if (!reader.IsDBNull(i))
            product.ProductNumber = reader.GetString(i);
        i = reader.GetOrdinal("SafetyStockLevel");
        if (!reader.IsDBNull(i))
            product.SafetyStockLevel = Convert.ToInt32(i);
        i = reader.GetOrdinal("ReorderPoint");
        if (!reader.IsDBNull(i))
            product.ReorderPoint = Convert.ToInt32(i);

        return product;
    }
}
